﻿Imports ComponentFactory.Krypton.Toolkit
Imports System.Data.OleDb
Imports System.Runtime.InteropServices
Imports CrystalDecisions.CrystalReports.Engine

Public Class ConsultaCliente
    Inherits KryptonForm

    Private conexao As OleDbConnection
    Private command As OleDbCommand
    Private dataSet As DataSet
    Private dataView As DataView
    Private dataAdapter As OleDbDataAdapter
    Private cmdBuilder As OleDbCommandBuilder
    Private strTipoFiltro = Nothing

#Region "Consultas SQL"
    Private strQueryosdois As String = "SELECT codCliente AS Código" & _
                                         " ,  nomeCliente AS Nome " & _
                                         ",  profissaoCliente AS Profissao " & _
                                         ", enderecoCliente AS Endereco" & _
                                         ", bairroCliente AS Bairro " & _
                                         ", tblEstado.siglaEstado AS Estado" & _
                                         ", cidadeCliente AS Cidade" & _
                                         ", numenderecoCliente AS Numero" & _
                                         ", complementoenderecoCliente AS Complemento" & _
                                         ", tblTipoLogradouro.descricaotipoLogradouro AS Logradouro" & _
                                         ", cepCliente AS CEP  " & _
                                         ", datanascCliente AS Data" & _
                                         ", rgCliente AS RG " & _
                                         ", cpfCliente AS CPF " & _
                                         ", inscricaoEstadualCliente AS Inscricao" & _
                                         ", cnpjCliente AS CNPJ" & _
                                         ", tblRamoAtividade.descricaoRamoAtividade AS Ramo" & _
                                         ", emailCliente AS Email" & _
                                         ", siteCliente AS Site" & _
                                         ", tipoCliente AS Tipo" & _
                                         " FROM (((tblCliente " & _
                                         " LEFT JOIN tblEstado ON tblEstado.codEstado = tblCliente.codEstado)" & _
                                         " LEFT JOIN tblTipoLogradouro ON tblTipoLogradouro.codtipoLogradouro = tblCliente.codtipoLogradouro)" & _
                                         " LEFT JOIN tblRamoAtividade ON tblRamoAtividade.codRamoAtividade = tblCliente.codRamoAtividade) "





    Private strQueryFisico As String = "SELECT codCliente AS Código" & _
                                        ", nomeCliente AS Nome" & _
                                        ", sexoCliente AS Sexo" & _
                                        ", dataNascCliente AS Nascimento" & _
                                        ", rgCliente AS RG" & _
                                        ", cpfCliente AS CPF" & _
                                        ", enderecoCliente AS Endereço" & _
                                        ", bairroCliente AS Bairro" & _
                                        ", cidadeCliente AS Cidade" & _
                                        ", tblEstado.siglaEstado AS Estado" & _
                                        " FROM tblCliente " & _
                                        " INNER JOIN tblEstado ON tblEstado.codEstado = tblCliente.codEstado" & _
                                        " WHERE tipoCliente = 'fisico'"

    Private strQueryJuridico As String = "SELECT codCliente AS Código" & _
                                        ", nomeCliente AS Nome" & _
                                        ", cnpjCliente AS CNPJ" & _
                                        ", enderecoCliente AS Endereço" & _
                                        ", bairroCliente AS Bairro" & _
                                        ", tblEstado.siglaEstado AS Estado" & _
                                        ", tblRamoAtividade.descricaoRamoAtividade AS Ramo" & _
                                        " FROM ((tblCliente" & _
                                        " INNER JOIN tblEstado ON tblEstado.codEstado = tblCliente.codEstado)" & _
                                        " INNER JOIN tblRamoAtividade ON tblRamoAtividade.codRamoAtividade = tblCliente.codRamoAtividade)" & _
                                        " WHERE tipoCliente = 'juridico'"
#End Region

    Sub New()

        ' This call is required by the Windows Form Designer.
        InitializeComponent()
        ' Add any initialization after the InitializeComponent() call.

        strTipoFiltro = "Nome like '%{0}%'"

        conexao = FabricaDeConexoes.CriarConexao()
        dataSet = New DataSet()


        carregarClientes()

    End Sub

    Private Sub carregarEstados()

        If (dataSet IsNot Nothing) Then
            dataAdapter = New OleDbDataAdapter(strQueryJuridico, conexao)
            dataAdapter.Fill(dataSet, "tblEstado")
        End If

    End Sub

    Private Sub carregarClientes()

        If (dataSet IsNot Nothing) Then
            dataAdapter = New OleDbDataAdapter(strQueryosdois, conexao)
            dataSet = New DataSet()
            dataAdapter.Fill(dataSet, "tblCliente")

            dataView = New DataView(dataSet.Tables("tblCliente"), Nothing, "Código", DataViewRowState.CurrentRows)
            dataGridView.DataSource = dataView
            dataGridView.Columns(0).Visible = False
        End If

    End Sub
    Private Sub carregarClienteJuridico()

        If (dataSet IsNot Nothing) Then
            dataAdapter = New OleDbDataAdapter(strQueryJuridico, conexao)
            dataSet = New DataSet()
            dataAdapter.Fill(dataSet, "tblCliente")

            dataView = New DataView(dataSet.Tables("tblCliente"), Nothing, "Código", DataViewRowState.CurrentRows)
            dataGridView.DataSource = dataView
            dataGridView.Columns(0).Visible = False
        End If

    End Sub

    Private Sub carregarClienteFisico()

        If (dataSet IsNot Nothing) Then
            dataAdapter = New OleDbDataAdapter(strQueryFisico, conexao)
            dataSet = New DataSet()
            dataAdapter.Fill(dataSet, "tblCliente")

            dataView = New DataView(dataSet.Tables("tblCliente"), Nothing, "Código", DataViewRowState.CurrentRows)
            dataGridView.DataSource = dataView
            dataGridView.Columns(0).Visible = False
        End If

    End Sub

    Private Sub excluirCliente()
        Try
            Dim queryAuto As String = "DELETE * FROM tblAuto WHERE tblAuto.codCliente = " & dataGridView.CurrentRow.Cells(0).Value
            Dim queryTelefone As String = "DELETE * FROM tblTelefoneCliente WHERE tblTelefoneCliente.codCliente = " & dataGridView.CurrentRow.Cells(0).Value
            Dim queryCliente As String = "DELETE FROM tblCliente WHERE codCliente = " & dataGridView.CurrentRow.Cells.Item("Código").Value

            'Abre conexão e exclui os veículos do cliente
            command = New OleDbCommand(queryAuto, conexao)
            conexao.Open()
            command.ExecuteNonQuery()

            'Exclui os telefones do cliente
            command.CommandText = queryTelefone
            command.ExecuteNonQuery()

            'Exclui o registro do cliente
            command.CommandText = queryCliente
            command.ExecuteNonQuery()

            'Atualiza o DataGridView
            dataGridView.Rows.Remove(dataGridView.CurrentRow)

        Catch ex As OleDbException
            Debug.WriteLine("Erro: " & ex.Message)
        Finally
            command.Dispose()
            conexao.Close()
        End Try
    End Sub

    Private Sub btnFechar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFechar.Click
        Me.Dispose()
    End Sub

    Private Sub btnNovo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNovo.Click
        Try
            Dim frmcad As New CadastroCliente()
            frmcad.ShowDialog()
            carregarClientes()

        Catch ex As Exception
            Debug.WriteLine(ex.StackTrace)
        End Try
    End Sub

    Private Sub cbFiltro_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cbFiltro.SelectedIndexChanged

        txtFiltrar.Clear()
        txtFiltrar.Focus()

        If (cbFiltro.SelectedItem.ToString() = "CNPJ") Then

            carregarClienteJuridico()
            strTipoFiltro = "CNPJ like '{0}%'"

        End If

        If (cbFiltro.SelectedItem.ToString() = "CPF") Then

            carregarClienteFisico()
            strTipoFiltro = "CPF like '{0}%'"
        End If

        If (cbFiltro.SelectedItem.ToString() = "RG") Then
            carregarClienteFisico()
            strTipoFiltro = "RG like '{0}%'"
        End If
        If (cbFiltro.SelectedItem.ToString() = "Nome") Then

            carregarClientes()
            strTipoFiltro = "Nome like '%{0}%'"

        End If
    End Sub

    Private Sub txtFiltrar_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtFiltrar.TextChanged
        dataView.RowFilter = String.Format(strTipoFiltro, txtFiltrar.Text)
    End Sub

    Private Sub btnExcluir_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExcluir.Click
        KryptonMessageBox.Show("Antes de excluir, verifique os dados do cliente.", "Atenção", MessageBoxButtons.OK, MessageBoxIcon.Information)
        btnImprimir.PerformClick()
        Dim resp = KryptonMessageBox.Show("Tem certeza que deseja excluir este cadastro?" & vbNewLine & "As alterações causadas pela exclusão são irreversíveis.", "Aviso", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
        If (resp = Windows.Forms.DialogResult.Yes) Then
            excluirCliente()
        End If
    End Sub

    Private Sub btnEditar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnEditar.Click
        Dim frm As New CadastroCliente(dataGridView.CurrentRow.Cells.Item(0).Value)
        frm.ShowDialog()
        Dim cmdBuilder As New OleDbCommandBuilder(dataAdapter)
        dataAdapter.Update(dataSet, "tblCliente")
        carregarClientes()
    End Sub

    Private Sub btnImprimir_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnImprimir.Click

        Dim cod = dataGridView.CurrentRow.Cells(0).Value
        Dim query = "SELECT tblCliente.*, tblEstado.descricaoEstado FROM (tblCliente INNER JOIN tblEstado ON tblEstado.codEstado = tblCliente.codEstado) WHERE tblCliente.codCliente = " & cod

        Dim ds As New DataSet()
        Dim da As New OleDbDataAdapter(query, conexao)
        da.Fill(ds, "tblCliente")
        da.SelectCommand.CommandText = "SELECT * FROM tblTelefoneCliente WHERE codCliente = " & cod
        da.Fill(ds, "tblTelefoneCliente")

        Dim urlDoc As String
        If (dataGridView.Columns.Contains("CNPJ")) Then
            If Not (String.IsNullOrEmpty(dataGridView.CurrentRow.Cells("CNPJ").Value)) Then
                urlDoc = Config.DiretorioAplicacao & "/../../../Relatorio/relatorioIndivCliJuri.rpt"
            Else
                urlDoc = Config.DiretorioAplicacao & "/../../../Relatorio/relatorioIndivCliFisi.rpt"
            End If
        Else
            urlDoc = Config.DiretorioAplicacao & "/../../../Relatorio/relatorioIndivCliFisi.rpt"
        End If

        Dim doc As New CrystalDecisions.CrystalReports.Engine.ReportDocument()

        doc.Load(urlDoc, CrystalDecisions.Shared.OpenReportMethod.OpenReportByDefault)
        doc.SetDataSource(ds)
        Dim frm As New VisualizadorAvancado()
        frm.setDocument(doc)
        frm.ShowDialog()

    End Sub

End Class